package org.jeecg.common.util.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExportExcelUtil {

    public static void exportExcel(String sheetname, String[] headers, String[] keys, Collection dataset,
                                   OutputStream out) {
        exportExcel(sheetname, headers, keys, dataset, out, "yyyy-MM-dd");
    }

    public static void exportExcel(String[] headers, Collection dataset,
                                   OutputStream out) {
        String[] keys = new String[headers.length];
        int key = 0;
        for (int i = 0; i < headers.length; i++) {
            keys[i] = key++ + "";
        }
        exportExcel("sheet0", headers, keys, dataset, out, "yyyy-MM-dd");
    }

    public static HSSFWorkbook exportReportExcel(String sheetname, String[] headers, String[] keys, Collection dataset,
                                                 OutputStream out) {
        return exportReportExcel(sheetname, headers, keys, dataset, out, "yyyy-MM-dd");
    }

    public static HSSFWorkbook exportReportExcelTwo(String sheetname, String[] headers, String[] keys, Collection dataset,
                                                    OutputStream out) {
        return exportReportExcelTwo(sheetname, headers, keys, dataset, out, "yyyy-MM-dd");
    }

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     *
     * @param title   表格标题名
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out     与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    public static void exportExcel(String title, String[] headers, String[] keys,
                                   Collection dataset, OutputStream out, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor((short) 40);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER );
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor((short) 20);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor((short) 43);
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBold(false);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        // 遍历集合数据，产生数据行
        Iterator it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Map o = (Map) it.next();
            for (short i = 0; i < keys.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Object value = o.get(keys[i]);
                // 判断值的类型后进行强制类型转换
                String textValue = null;
                if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else {
                    // 其它数据类型都当作字符串简单处理
                    if (value == null) {
                        textValue = "";
                    } else {
                        textValue = value.toString();
                    }
                }
                if (textValue != null) {
                    Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                    Matcher matcher = p.matcher(textValue);
                    if (matcher.matches()) {
                        // 是数字当作double处理
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        cell.setCellValue(textValue);
                    }
                }
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     *
     * @param title   表格标题名
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out     与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    public static HSSFWorkbook exportReportExcel(String title, String[] headers, String[] keys,
                                                 Collection dataset, OutputStream out, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor((short) 40);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor((short) 40);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor((short) 9);
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBold(false);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            sheet.setColumnWidth(i, 256 * 40);
        }

        // 遍历集合数据，产生数据行
        Iterator it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Map o = (Map) it.next();
            for (short i = 0; i < keys.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Object value = o.get(keys[i]);
                // 判断值的类型后进行强制类型转换
                String textValue = null;
                if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else {
                    // 其它数据类型都当作字符串简单处理
                    if (value == null) {
                        textValue = "";
                    } else {
                        textValue = value.toString();
                    }
                }
                if (textValue != null) {
                    Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                    Matcher matcher = p.matcher(textValue);
                    if (matcher.matches()) {
                        // 是数字当作double处理
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        cell.setCellValue(textValue);
                    }
                }
            }
        }

        return workbook;
    }

    /**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     *
     * @param title   表格标题名
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param out     与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    public static HSSFWorkbook exportReportExcelTwo(String title, String[] headers, String[] keys,
                                                    Collection dataset, OutputStream out, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor((short) 40);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor((short) 9);
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor((short) 9);
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBold(false);
        // 把字体应用到当前的样式
        style2.setFont(font2);
        //合并单元行
        CellRangeAddress region = new CellRangeAddress(2, 10, 0,  0);
        //参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        sheet.addMergedRegion(region);
        CellRangeAddress region1 = new CellRangeAddress(11, 13, 0, 0);
        //参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
        sheet.addMergedRegion(region1);

        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("leno");

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            sheet.setColumnWidth(i, 256 * 35);
        }

        // 遍历集合数据，产生数据行
        Iterator it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Map o = (Map) it.next();
            for (short i = 0; i < keys.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Object value = o.get(keys[i]);
                // 判断值的类型后进行强制类型转换
                String textValue = null;
                if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else {
                    // 其它数据类型都当作字符串简单处理
                    if (value == null) {
                        textValue = "";
                    } else {
                        textValue = value.toString();
                    }
                }
                if (textValue != null) {
                    Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                    Matcher matcher = p.matcher(textValue);
                    if (matcher.matches()) {
                        // 是数字当作double处理
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        cell.setCellValue(textValue);
                    }
                }
            }
        }
        return workbook;
    }

    public static void main(String[] args) {
        // 测试学生
        String[] headers =
                {"学号", "姓名", "年龄", "出生日期"};
        String[] keys =
                {"a", "b", "c", "e"};
        List dataset = new ArrayList();
        for (int i = 0; i < 10; i++) {
            Map m = new HashMap();
            m.put("0", 10000001);
            m.put("1", "张三" + i);
            m.put("2", 20 + i);
            m.put("3", new Date());
            dataset.add(m);
        }
        try {
            OutputStream out = new FileOutputStream("d://b.xls");
            ExportExcelUtil.exportExcel(headers, dataset, out);
            out.close();
            System.out.println("excel导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel核心类
     *
     * @param cols     需要导出的列
     * @param title    表名称
     * @param headers  列名称
     * @param fileName 文件名
     * @param lists    需要导出的内容
     * @param response
     * @throws IOException
     */
    public static void exportExcel(String[] cols, String title, String[] headers, String fileName, List<Map<String, Object>> lists, HttpServletResponse response) {
        try {
            response.setContentType("application/msExcel");
            response.setHeader("Content-Disposition", "" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));
            Workbook wb = null;
            if (fileName.endsWith(".xls")) {
                wb = new HSSFWorkbook();
            }
            Integer size = headers.length;
            Sheet s = wb.createSheet(title);
            for (Integer i = 1; i < (size + 1); i++) {
                s.setColumnWidth(i, 256 * 25);
            }
            Row r = null;
//            s.createRow(0).createCell(0).setCellValue(title);//创建第一行createRow(0),第一列createCell(0)为标题
            r = s.createRow(0);
            //添加列名
            for (int j = 0; j < headers.length; j++) {
                r.createCell(j).setCellValue(headers[j]);
            }
            for (int rownum = 0; rownum < lists.size(); rownum++) {
                r = s.createRow(rownum + 1);
                Map<String, Object> map = (Map<String, Object>) lists.get(rownum);
//                r.createCell(0).setCellValue(rownum + 1);
                int n = 0;
                for (String key : cols) {
                    r.createCell(n).setCellValue(map.get(key) != null ? map.get(key).toString() : "");
                    n++;
                }
            }
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel核心类
     *
     * @param title    表名称
     * @param headers  列名称
     * @param fileName 文件名
     * @param lists    需要导出的内容
     * @param response
     * @throws IOException
     */
    public static void exportExcel(String title, String fileName, String[] headers, List<Map<String, Object>> lists, HttpServletResponse response) {
        String[] keys = new String[headers.length];
        for (int i = 0; i < headers.length; i++) {
            keys[i] = i + "";
        }
        exportExcel(keys, title, headers, fileName, lists, response);
    }

    /**
     * 导出excel核心类
     *
     * @param title    表名称
     * @param lists    需要导出的内容
     * @param response
     * @throws IOException
     */
    public static void exportExcel(String title, List<String[]> lists, HttpServletResponse response) throws RuntimeException {
        try {
            response.setContentType("application/msExcel");
            response.setHeader("Content-Disposition", title + ".xls");
            Workbook wb = new HSSFWorkbook();
            Integer size = lists.get(0).length;
            Sheet s = wb.createSheet(title);
            for (Integer i = 1; i < (size + 1); i++) {
                s.setColumnWidth(i, 256 * 25);
            }
            Row r = null;
//            s.createRow(0).createCell(0).setCellValue(title);//创建第一行createRow(0),第一列createCell(0)为标题
            r = s.createRow(0);
            CellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //添加列名
            for (int j = 0; j < lists.get(0).length; j++) {
                Cell celltemp = r.createCell(j);
                celltemp.setCellStyle(style);
                celltemp.setCellValue(lists.get(0)[j]);
            }
            for (int rownum = 1; rownum < lists.size(); rownum++) {
                r = s.createRow(rownum);
                String[] vals = lists.get(rownum);
//                r.createCell(0).setCellValue(rownum );
                for (int x = 0; x < vals.length; x++) {
                    r.createCell(x).setCellValue(vals[x] != null ? vals[x] : "");
                }
            }
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("文件格式错误");
        }
    }

    /**
     * 导出excel核心类
     *
     * @param title    表名称
     * @param lists    需要导出的内容
     * @param response
     * @throws IOException
     */
    public static void exportExcel(String title, List<String[]> head, List<String[]> lists, HttpServletResponse response) {
        try {
            response.setContentType("application/msExcel");
            response.setHeader("Content-Disposition", title + ".xls");
            Workbook wb = new HSSFWorkbook();
            Integer size = lists.get(0).length;
            Sheet s = wb.createSheet(title);
            for (Integer i = 1; i < (size + 1); i++) {
                s.setColumnWidth(i, 200 * 25);
            }
            Row r = null;
            CellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            r = s.createRow(0);//创建第一行,主表标题
            //添加主表列名
            for (int j = 0; j < head.get(0).length; j++) {
                Cell celltemp = r.createCell(j);
                celltemp.setCellStyle(style);
                celltemp.setCellValue(head.get(0)[j]);
            }
            r = s.createRow(1);//创建第2行,主表内容
            for (int j = 0; j < head.get(0).length; j++) {
                r.createCell(j).setCellValue(head.get(1)[j]);
            }
            r = s.createRow(2);
            for (int j = 0; j < lists.get(0).length; j++) { //添加子表列名
                Cell celltemp = r.createCell(j);
                celltemp.setCellStyle(style);
                celltemp.setCellValue(lists.get(0)[j]);
            }
            for (int rownum = 1; rownum < lists.size(); rownum++) { //循环添加子表内容
                r = s.createRow(rownum + 2);
                String[] vals = lists.get(rownum);
                for (int x = 0; x < vals.length; x++) {
                    r.createCell(x).setCellValue(vals[x] != null ? vals[x] : "");
                }
            }
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String exportExcel(String title, List<String[]> head, List<String[]> lists) {
        try {
            Workbook wb = new HSSFWorkbook();
            Integer size = ((String[]) lists.get(0)).length;
            Sheet s = wb.createSheet(title);

            Integer i;
            for (i = 1; i < size + 1; i = i + 1) {
                s.setColumnWidth(i, 5000);
            }

            i = null;
            CellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//            style.setFillPattern(FillPatternType.forInt(1));
            Row r = s.createRow(0);

            int rownum;
            Cell celltemp;
            for (rownum = 0; rownum < ((String[]) head.get(0)).length; ++rownum) {
                celltemp = r.createCell(rownum);
                celltemp.setCellStyle(style);
                celltemp.setCellValue(((String[]) head.get(0))[rownum]);
            }

            r = s.createRow(1);

            for (rownum = 0; rownum < ((String[]) head.get(0)).length; ++rownum) {
                r.createCell(rownum).setCellValue(((String[]) head.get(1))[rownum]);
            }

            r = s.createRow(2);

            for (rownum = 0; rownum < ((String[]) lists.get(0)).length; ++rownum) {
                celltemp = r.createCell(rownum);
                celltemp.setCellStyle(style);
                celltemp.setCellValue(((String[]) lists.get(0))[rownum]);
            }

            for (rownum = 1; rownum < lists.size(); ++rownum) {
                r = s.createRow(rownum + 2);
                String[] vals = (String[]) lists.get(rownum);

                for (int x = 0; x < vals.length; ++x) {
                    r.createCell(x).setCellValue(vals[x] != null ? vals[x] : "");
                }
            }

            String file = "./" + title + ".xls";
            File f = new File(file);
            if (f.exists()) {
                f.delete();
            }

            f.createNewFile();
            OutputStream out = new FileOutputStream(file);
            wb.write(out);
            out.flush();
            out.close();
            return file;
        } catch (Exception var11) {
            var11.printStackTrace();
            return null;
        }
    }
}
